package school.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import school.entity.Page;
import school.entity.Student;
import school.util.DBUtil;

public class MySqlStudentDao implements StudentDao {

	@Override
	public int add(Student s) {
		String sql = "";
		return 0;
	}

	@Override
	public int del(long stuno) {
		String sql = "delete from student where stuno=?";

		return DBUtil.executeUpdate(sql, stuno);
	}

	@Override
	public int update(Student s) {
		String sql = "update student set sname=?,sex=?,fromcity=?,enterdate=? where stuno=?";
		return DBUtil.executeUpdate(sql, s.getSname(), s.getSex(),
				s.getFromcity(), s.getEnterdate(), s.getStuno());
	}

	public int update2(Student s) {
		String sql = "update student set sname=?,sex=?,fromcity=?,enterdate=? where stuno=?";
		return DBUtil.executeUpdate(sql, s.getSname(), s.getSex(),
				s.getFromcity(), s.getEnterdate(), s.getStuno());
	}

	// 根据主键查找单个对象
	@Override
	public Student find(Long stuno) {
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student s = null;
		String sql = "select * from student where stuno=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, stuno);
			rs = ps.executeQuery();
			if (rs.next()) {
				s = new Student();
				s.setStuno(rs.getLong("stuno"));
				s.setSname(rs.getString("sname"));
				// .....
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		return s;
	}

	// 查找全部对象
	@Override
	public List<Student> query() {
		String sql = "select * from student ";
		List<Student> datas = new ArrayList<Student>();
		// 如果表为空，返回空集合，不是null
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				// 读取一行，封装成Student对象，放入集合
				Student st = new Student();
				st.setStuno(rs.getLong("stuno"));
				st.setSname(rs.getString("sname"));
				// ..........
				// !!!!!!!!!!!
				datas.add(st);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}

		return datas;
	}

	@Override
	public Student login(Long stuno, String pass) {

		String sql = "select * from student where stuno=? and pass=?";

		// 如果表为空，返回空集合，不是null
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, stuno);
			ps.setString(2, pass);
			rs = ps.executeQuery();
			if (rs.next()) {
				// 读取一行，封装成Student对象，放入集合
				Student st = new Student();
				st.setStuno(rs.getLong("stuno"));
				st.setSname(rs.getString("sname"));
				st.setSex(rs.getString("sex"));
				st.setBrithday(rs.getDate("birthday"));
				// ...
				return st;
			}
		} catch (SQLException e) {
			e.printStackTrace();// log 4j
		} finally {
			DBUtil.close(rs, ps, con);
		}

		return null;
	}

	// public List<Student> query(int pageNo) {
	// return null;
	// }

	public Page<Student> query(int pageNo) {
		Page<Student> p = new Page<Student>();
		p.setPageNo(pageNo);

		String sql = "select * from student limit ?,?";
		List<Student> datas = new ArrayList<Student>();
		// 如果表为空，返回空集合，不是null
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNo - 1) * Page.PAGESIZE);
			ps.setInt(2, Page.PAGESIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				// 读取一行，封装成Student对象，放入集合
				Student st = new Student();
				st.setStuno(rs.getLong("stuno"));
				st.setSname(rs.getString("sname"));
				// ..........
				// !!!!!!!!!!!
				datas.add(st);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, null);
		}
		p.setDatas(datas);
		// 总页数
		String sql2 = "select count(*) from student";
		int total = 0;
		try {
			ps = con.prepareStatement(sql2);

			rs = ps.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, con);
		}
		p.setTotalLines(total);
		return p;
	}
}
